The present invention relates to a method for generating queries, a query generator, and to an information management system, in particular for use with relational databases.
Query generators, for example Discoverer® from Oracle® International Corporation of Redwood Shores, Calif., are used to construct database queries which are then sent to a database for execution. A user constructs a query by selecting items from a drop down list of items displayed on the screen. The items may represent data which are to be obtained from the database, or alternatively they may represent operations that are to be performed on these data. Once the items have been selected, the query generator then generates a query, usually in Structured Query Language (SQL), for execution by the database.
It is possible for a user to construct a SQL query that evaluates returned data items to see whether they meet a predefined condition. For example, a query may return data from a database table that represent the value of sales that have been made in each month of a year, and the SQL statement may incorporate a conditional expression that filters out all values for which the value of sales is below a certain amount. Other types of SQL statements may cause a calculation to be performed on the data. For example, a percentage value may be calculated for each of the returned sales values as a proportion of the total sales for all the months of the year. This percentage value would typically be presented to the user in the form of an extra column displayed in the table adjacent the returned value of the sales for each month.
However, this represents a rather inflexible approach since the query operates in exactly the same way each time it is executed, and it is therefore necessary to generate totally separate queries for all types of filtering and calculation operation which may be required. Obviously, this increases significantly the effort required to build and maintain the queries, and requires increased storage capacity for storing the queries.
A query may also be used as a data retrieval engine for a report generator. The report generator is typically configured to format and output the subset of data retrieved by the query. This output may be displayed on screen or a hard copy may be printed. A user or administrator will generate a new query when they initially configure a report.
In large data warehousing applications, database tables are often used which number many millions of records. In these situations hundreds of thousands of records can be returned if a user does not significantly filter the data returned from an executed query. Such results can be difficult to manage and severely occupy system resources.
Also in these applications, which are often utilized in large business or public organizations, a number of hard copy reports are often required at regular time intervals to provide a continued flow of information or keep abreast of any changes within the database tables. The creation of these reports is often a time consuming process of considerable complexity and thus the reports are often generated by computer professionals at considerable expense.